import java.util.ArrayList;
import java.util.HashMap;
import java.util.TreeMap;
import java.util.Map;

class StuScoreManager{
    //数据库配置信息
    private String DB_NAME = "hw19";
    private String USERNAME = "root";
    private String PASSWORD = "";
    private String TABLE_NAME = "scores";
    private final int EXAM_TIME = 4;
    
    //数据库管理器
    private DBManager dbm;
    //数据封装集
    private ArrayList<HashMap<String, Object>> allData;    
    //封装到TreeMap中
    private TreeMap<Stu, Integer> allData_tm;
    
    public StuScoreManager(){
        String url = "jdbc:mysql://localhost:3306/" + DB_NAME + "?useSSL=false";
        try{
            //实例化dbmanager
            this.dbm = new DBManager(url, USERNAME, PASSWORD);
            //获取封装结果集
            String sql = "select * from " + TABLE_NAME;
            allData = new ArrayList<HashMap<String, Object>>();
            this.dbm.encapsolateResultSet(this.allData, sql);
            //将结果集封装到TreeMap中
            allData_tm = new TreeMap<Stu, Integer>();
            int i = 1;
            for(HashMap<String, Object> hm : allData){
                Stu stu = new Stu(hm);
                allData_tm.put(stu, i++);
            }
                        
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    //获取某次考试总成绩
    public int getAllScore_by_time(int n)
    throws Exception{
        int ans = 0;
        try{
            HashMap<String, ArrayList<Object>> hm = getColumn("score0" + n);
            ArrayList<Object> list = hm.get("score0" + n);
            for(Object i : list){
                ans += (Integer)i;
            }
            System.out.println("第" + n + "次考试总成绩为：" + ans);
            
            return ans;
        }catch(Exception e){
            throw e;
        }
    }
    
    //获取某个学生总成绩
    public int getAllScore_by_stu(String name)
    throws Exception{
        int ans = 0;
        try{
            int index = getRowIndex(name);
            HashMap<String, Object> hm = getRow(index);
            for(Map.Entry<String, Object> entry : hm.entrySet()){
                if(entry.getKey().matches("score0[0-9]")){
                    ans += (Integer)entry.getValue();
                }
            }
            System.out.println(name + "的总成绩为：" + ans);
            
            return ans;
        }catch(Exception e){
            throw e;
        }
    }
    
    //获取某个学生平均成绩
    public double getAVGScore_by_stu(String name)
    throws Exception{
        try{
            int ans = getAllScore_by_stu(name);
            double avg = ((double)ans) / EXAM_TIME;
            System.out.println(name + "平均成绩为：" + avg);
            
            return avg;
        }catch(Exception e){
            throw e;
        }
    }
    
    //获取平均成绩最大的考试次数与平均成绩
    public void getMaxAVGScore_by_time(){
        int[] ans = new int[EXAM_TIME];
        for(HashMap<String, Object> hm : allData){
            for(int i = 0; i < EXAM_TIME; i++){
                ans[i] += (Integer)hm.get("score0" + (i + 1));
            }
        }
        
        int time = 0 + 1;
        int max = ans[0];
        for(int i = 1; i < ans.length; i++){
            max = ans[i] > max ? ans[i] : max;
            time = ans[i] > max ? (i + 1) : time;
        }        
        
        System.out.println("第" + time + "次平均分最高: " + ((double)max / EXAM_TIME));
    }
    
    //获取某个学生某次考试成绩
    public int getScore_by_stu(String name, int n)
    throws Exception{
        try{
            int index = getRowIndex(name);
            HashMap<String, Object> hm = getRow(index);
            HashMap<String, ArrayList<Object>> colMap = getColumn("score0" + n, hm);
            int score = (Integer)colMap.get("score0" + n).get(0);
            System.out.println(name + "在第" + "次考试成绩为：" + score);
            
            return score;
        }catch(Exception e){
            throw e;
        }
    }
    
    //由TreeMap封装集，排序stu
    public void viewBySort(){
         for(Map.Entry<Stu, Integer> entry : allData_tm.entrySet()){
            System.out.println(entry.getKey() + " : " + entry.getValue());
        }
    }
    
    //根据学生姓名（该题中学生名作为唯一标识）获取该学生记录在结果集中的索引
    private int getRowIndex(String name)
    throws Exception{
        try{
            int i = 0;
            for(HashMap<String, Object> mapItem : allData){
                if(mapItem.get("name").equals(name)){
                    return i;
                }
                ++i;
            }
        }catch(Exception e){
            throw e;
        }
        
        return -1;
    }
    
    //根据索引获取某一行HashMap
    private HashMap<String, Object> getRow(int n)
    throws Exception{
        try{
            return allData.get(n);
        }catch(Exception e){
            throw e;
        }
    }
    
    //获取某列所有成绩
    private HashMap<String, ArrayList<Object>> getColumn(String colName)
    throws Exception{
        HashMap<String, ArrayList<Object>> hmTmp = new HashMap<String, ArrayList<Object>>();
        ArrayList<Object> list = new ArrayList<Object>();
        try{
            for(HashMap<String, Object> hm : allData){
                list.add(hm.get(colName));
            }
            
            hmTmp.put(colName, list);
            return hmTmp;
        }catch(Exception e){
            throw e;
        }
    }
    
    //根据某条记录获取某列的成绩
    private HashMap<String, ArrayList<Object>> getColumn(String colName, HashMap<String, Object> hm)
    throws Exception{
        HashMap<String, ArrayList<Object>> hmTmp = new HashMap<String, ArrayList<Object>>();
        ArrayList<Object> list = new ArrayList<Object>();
        try{
            list.add(hm.get(colName));
            hmTmp.put(colName, list);
            return hmTmp;
        }catch(Exception e){
            throw e;
        }
    }
}